package com.baylor.server.business.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.baylor.server.business.entity.CioeCompany;

public interface CioeCompanyRespository extends PagingAndSortingRepository<CioeCompany,Integer>,JpaSpecificationExecutor<CioeCompany> {

	@Query("from CioeCompany c where c.hkid=?1 and c.lang=?2 and (c.hkdate like %?3%) ")
	public List<CioeCompany> findCompanyByHkid(String hkid,String lang, String now);

	@Query("from CioeCompany c where c.hkid=?1  and (c.hkdate like %?2%)")
	public List<CioeCompany> findCompanyListByHkid(String hkid,  String now);
	
	@Query("from CioeCompany where admin=?1 and power=?2 and lang=?3 ")
	public List<CioeCompany> appLogin(String userLoginName, String userPassword, String lang);
	
	
	
	@Query("from CioeCompany where hkid=?1 and lang='1' and hkdate like ?2%")
	public List<CioeCompany> qrcodeExportCn(String hkid,String hkdate);
	@Query("from CioeCompany where hkid=?1 and lang='2' and hkdate like ?2%")
	public List<CioeCompany> qrcodeExportEn(String hkid,String hkdate);
	
	
	
	@Query("from CioeCompany c where c.hkid=?1 and hkdate like ?2%")
	public List<CioeCompany> findid(String ownerId,String now);

	/*@Query("select id,hkid,admin,power,hkname,pic,boothArea,boothNo,hkinfo,hktel_guoji,country,hktel_quhao,province"
			+ ",city,hktel,hkfax_guoji,hkfax_quhao, hkfax,hkemail,hkaddress, hkurl,hkdate,lang, internationalArea,spec,zhanQu,prizeNum   from CioeCompany where ((hkinfo like %?1% or boothNo like %?1%) and lang=?2 and hkdate like %?4%) or(lang=?2  and boothArea=?3 and hkdate like %?4%)" )
	*/
	@Query(" from CioeCompany  where  lang=?1   and hkdate like ?3% and boothArea=?2" )
	public List<CioeCompany> companyListPhoneWithboothArea( String lang,String boothArea,String now);

	@Query(" from CioeCompany  where   hkdate like ?3% and  (hkname like %?1% or hkinfo like %?1% or boothNo like %?1% ) and  lang=?2 order by hkname ,hkinfo,boothNo DESC" )
	public List<CioeCompany> companyListPhoneWithKey(String key, String lang,String now);
	
	@Query("select distinct province from CioeCompany where hkdate like %?1% and boothArea=?2  and country='中国'   and province<>null")
	public List<String> companyAddressPhone(String now,String boothArea);
	
	@Query("select distinct province from CioeCompany where hkdate like %?1% and boothArea=?2  and country='China'   and province<>null")
	public List<String> companyAddressPhoneEn(String now,String boothArea);

//	@Query("select distinct country from CioeCompany where hkdate like %?1% and (country<>'中国'  and country<>'China' ) and lang=?2")
	@Query("from CioeCompany where hkdate like %?1% and (hktel_guoji<>'86'  and hktel_guoji<>'+86' ) and lang=?2  and boothArea=?3")
	public List<CioeCompany> companyAddressOutPhone(String now ,String lang,String boothArea);

	@Query("from CioeCompany where spec=?1 and boothArea=?2  and hkdate like ?3% and lang=?4")
	public List<CioeCompany> companySpecPhone(Integer spec, String boothArea, String format, String lang);

	@Query("from CioeCompany where province like %?1% and boothArea=?2 and hkdate like ?3% and lang=?4")
	public List<CioeCompany> companyAreaNameList(String areaName,String boothArea, String now, String lang);

	@Query("from CioeCompany where hkid=?1 and hkdate like ?2%")
	public List<CioeCompany> findByhkid(String hkid,String now);
	/**
	 * 取得指定年份和行业的展商用户
	 * @param boothArea
	 * @param year
	 * @return
	 */
	@Query("from CioeCompany c where c.boothArea=?1 and c.hkdate like %?2% and c.lang=1")
	public List<CioeCompany> getCioeCompanyListByBoothArea(String boothArea,int year);
	

	
	@Query("from CioeCompany where hkdate like ?1%   order by id")
	public List<CioeCompany> findNowCompany(String now );
	@Query("from CioeCompany where hkdate like ?1% and boothArea=?2  order by id")
	public List<CioeCompany> findNowCompany(String now,String boothArea);
	@Query("from CioeCompany where hkdate like ?1% and boothArea=?2 and boothNo like %?3% order by id")
	public List<CioeCompany> findNowCompany(String now,String boothArea,String boothNo);

	@Query("from CioeCompany where hkdate like ?1% and boothNo like %?2% order by id")
	public List<CioeCompany> findNowBoothNoCompany(String now,String boothNo);
	/**
	 * 取出指定年份的所有展商用户
	 * @param year
	 * @return
	 */
	@Query("from CioeCompany c where c.hkdate like %?1%")
	public List<CioeCompany> getCioeCompanyAllUser(int year);
	
	@Query("from CioeCompany c where c.hkdate like %?1% and c.lang=1")
	public List<CioeCompany> getZhCioeCompanyAllUser(int year);
	
	@Query("from CioeCompany c where c.dateSearch>=?1 and c.dateSearch<=?2 and c.lang=?3")
	public List<CioeCompany> getCioeCompanyByTime(String startTime,String endTime,String lang);
	
	@Query("from CioeCompany c where c.hkdate like %?1% ")
	public List<CioeCompany> allCompanyThisYear(String year); 

	@Query("from CioeCompany c where c.hkdate like %?1% and updateDate>=?2")
	public List<CioeCompany> updateCompanyThisYear(String year,String date);
	
	

}
